package dao.mysql;

import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import model.BookingDTO;
import model.BookingSearchDTO;
import dao.BookingDAO;
import dao.DAOException;
import dao.mysql.util.MySQLUtility;

public class BookingDAOImpl implements BookingDAO
{

	public void insertBooking(BookingDTO data) throws DAOException
	{
		Connection conn = null;

		try
		{
			conn = MySQLUtility.getConnection();
			conn.setAutoCommit(false);
		} catch (ClassNotFoundException e1)
		{
			// TODO Auto-generated catch block
			e1.printStackTrace();

		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try
		{

			String sql = "Insert Into Booking Values (?,?,?,?,?,?,?,?,?,?,?,?,?) ";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, data.getId());
			ps.setInt(2, data.getPriority());
			ps.setDate(3, (Date) data.getStartDate());
			ps.setDate(4, (Date) data.getEndDate());
			ps.setString(5, data.getContactDetail());
			ps.setString(6, data.getReason());
			ps.setString(7, data.getValidateBy());
			ps.setDate(8, (Date) data.getValidateOn());
			ps.setString(9, data.getStatus());
			ps.setString(10, data.getUserId());
			ps.setBoolean(11, data.getEmergency());
			ps.setString(12, data.getFacilityName());
			ps.setDate(13, (Date) data.getBookOn());

			ps.executeUpdate();

			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing insert: " + sql);
			conn.commit();
			ps.close();
		} catch (Exception e)
		{
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally
		{
			try
			{
				conn.rollback();
				conn.close();
			} catch (Exception e)
			{
			}
		}
	}

	@Override
	public BookingDTO findBooking(String id) throws DAOException
	{
		// TODO Auto-generated method stub
		Connection conn = null;

		try
		{
			conn = MySQLUtility.getConnection();
			conn.setAutoCommit(false);
		} catch (ClassNotFoundException e1)
		{
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		BookingDTO booking = null;
		try
		{

			String sql = "select*from Booking where Id=? ";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, id);
			ResultSet rs = ps.executeQuery();
			while (rs.next())
			{
				booking = new BookingDTO();
				booking.setBookOn(rs.getDate("bookOn"));
				booking.setContactDetail(rs.getString("contactDetail"));
				booking.setEmergency(rs.getBoolean("Emergency"));
				booking.setEndDate(rs.getDate("EndDate"));
				booking.setFacilityName(rs.getString("FacilityName"));
				booking.setId(rs.getString("Id"));
				booking.setPriority(rs.getInt("Priority"));
				booking.setReason(rs.getString("Reason"));
				booking.setStartDate(rs.getDate("StartDate"));
				booking.setStatus(rs.getString("Status"));
				booking.setUserId(rs.getString("UserId"));
				booking.setValidateBy(rs.getString("ValidateBy"));
				booking.setValidateOn(rs.getDate("ValidateOn"));

			}

			/*
			 * Logger.getLogger(getClass().getName()).log(Level.INFO,
			 * "Executing insert: " + sql);
			 */
			conn.commit();
			ps.close();

		} catch (Exception e)
		{
			String error = "Error selecting user. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);

		} finally
		{
			try
			{
				conn.rollback();
				conn.close();
			} catch (Exception e)
			{
			}
		}
		return booking;
	}

	@Override
	public ArrayList<BookingDTO> getAllBooking() throws DAOException
	{
		// TODO Auto-generated method stub
		Connection conn = null;

		try
		{
			conn = MySQLUtility.getConnection();
			conn.setAutoCommit(false);
		} catch (ClassNotFoundException e1)
		{
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		BookingDTO booking = null;
		ArrayList<BookingDTO> list = new ArrayList<BookingDTO>();
		try
		{

			String sql = "select * from Booking";
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next())
			{
				booking = new BookingDTO();
				booking.setBookOn(rs.getDate("bookOn"));
				booking.setContactDetail(rs.getString("contactDetail"));
				booking.setEmergency(rs.getBoolean("Emergency"));
				booking.setEndDate(rs.getDate("EndDate"));
				booking.setFacilityName(rs.getString("FacilityName"));
				booking.setId(rs.getString("Id"));
				booking.setPriority(rs.getInt("Priority"));
				booking.setReason(rs.getString("Reason"));
				booking.setStartDate(rs.getDate("StartDate"));
				booking.setStatus(rs.getString("Status"));
				booking.setUserId(rs.getString("UserId"));
				booking.setValidateBy(rs.getString("ValidateBy"));
				booking.setValidateOn(rs.getDate("ValidateOn"));
				list.add(booking);
			}

			/*
			 * Logger.getLogger(getClass().getName()).log(Level.INFO,
			 * "Executing insert: " + sql);
			 */
			conn.commit();
			ps.close();

		} catch (Exception e)
		{
			String error = "Error selecting booking. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);

		} finally
		{
			try
			{
				conn.rollback();
				conn.close();
			} catch (Exception e)
			{
			}
		}
		return list;

	}

	@Override
	public void updateBooking(BookingDTO booking) throws DAOException
	{
		// TODO Auto-generated method stub
		Connection conn = null;
		String sql = null;
		PreparedStatement ps = null;
		try
		{
			conn = MySQLUtility.getConnection();
		} catch (ClassNotFoundException e1)
		{
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try
		{
			System.out.println(booking.getStatus());

			sql = "Update Booking Set ContactDetail=?, Priority=?,FacilityName=?,StartDate=?,EndDate=?,Reason=?,Emergency=?,Status=?,ValidateBy=?, ValidateOn=? where Id=?";
			ps = conn.prepareStatement(sql);

			ps.setString(1, booking.getContactDetail());
			ps.setInt(2, booking.getPriority());
			ps.setString(3, booking.getFacilityName());
			ps.setDate(4, (Date) booking.getStartDate());
			ps.setDate(5, (Date) booking.getEndDate());
			ps.setString(6, booking.getReason());
			ps.setBoolean(7, (Boolean) booking.getEmergency());
			ps.setString(8, booking.getStatus());
			ps.setString(9, booking.getValidateBy());
			ps.setDate(10, (Date) booking.getValidateOn());
			ps.setString(11, booking.getId());
			ps.executeUpdate();

			/*
			 * Logger.getLogger(getClass().getName()).log(Level.INFO,
			 * "Executing insert: " + sql);
			 */

			ps.close();
		} catch (Exception e)
		{
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally
		{
			try
			{
				conn.rollback();
				conn.close();
			} catch (Exception e)
			{
			}
		}
	}

	public ArrayList<BookingDTO> findBookingByCriteria(BookingSearchDTO bs)
			throws DAOException
	{
		Connection conn = null;
		try
		{
			conn = MySQLUtility.getConnection();
			conn.setAutoCommit(false);
		} catch (ClassNotFoundException e1)
		{
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		ArrayList<BookingDTO> list = new ArrayList<BookingDTO>();
		try
		{
			conn.setAutoCommit(false);
			String sql = "";
			String where = "";
			ArrayList params = new ArrayList();

			sql = "SELECT * FROM Booking ";

			if (bs.getPriority() != null)
			{
				where = " WHERE ";
				where += " Priority LIKE ? ";
				params.add("%" + bs.getPriority() + "%");
			}
			if (bs.getStartDate() != null
					&& !bs.getStartDate().toString().isEmpty())
			{
				if (where.isEmpty())
					where = " WHERE ";
				else
					where += " AND ";
				where += " StartDate LIKE ? ";
				params.add("%" + bs.getStartDate() + "%");

			}
			if (bs.getEndDate() != null
					&& !bs.getEndDate().toString().isEmpty())
			{
				if (where.isEmpty())
					where = " WHERE ";
				else
					where += " AND ";
				where += " EndDate LIKE ? ";
				params.add("%" + bs.getEndDate() + "%");

			}
			if (bs.getValidateBy() != null && !bs.getValidateBy().isEmpty())
			{
				if (where.isEmpty())
					where = " WHERE ";
				else
					where += " AND ";
				where += " ValidateBy LIKE ? ";
				params.add("%" + bs.getValidateBy() + "%");

			}
			if (bs.getEmergency() != null
					&& !bs.getEmergency().toString().isEmpty())
			{
				if (where.isEmpty())
					where = " WHERE ";
				else
					where += " AND ";
				where += " Emergency LIKE ? ";
				params.add("%" + bs.getEmergency() + "%");

			}
			if (bs.getStatus() != null && !bs.getStatus().isEmpty())
			{
				if (where.isEmpty())
					where = " WHERE ( ";
				else
					where += " AND ( ";
				ArrayList<String> statusList = bs.getStatus();

				int i = 0;
				for (String s : statusList)
				{
					if (i > 0)
						where += " OR ";
					where += " Status = ? ";
					params.add(s);
					i++;
				}

				where += " ) ";
			}
			if (bs.getUserId() != null && !bs.getUserId().isEmpty())
			{
				if (where.isEmpty())
					where = " WHERE ";
				else
					where += " AND ";
				where += " UserId LIKE ? ";
				params.add("%" + bs.getUserId() + "%");

			}
			if (bs.getFacilityName() != null && !bs.getFacilityName().isEmpty())
			{
				if (where.isEmpty())
					where = " WHERE ";
				else
					where += " AND ";
				where += " FacilityName LIKE ? ";
				params.add("%" + bs.getFacilityName() + "%");

			}

			PreparedStatement prep = conn.prepareStatement(sql + where);
			for (int i = 1; i <= params.size(); i++)
			{
				prep.setObject(i, params.get(i - 1));
			}

			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + sql + where);
			ResultSet rs = prep.executeQuery();
			while (rs.next())
			{
				BookingDTO booking = new BookingDTO();
				booking.setPriority(rs.getInt("Priority"));
				booking.setStartDate(rs.getDate("StartDate"));
				booking.setEndDate(rs.getDate("EndDate"));
				booking.setValidateBy(rs.getString("ValidateBy"));
				booking.setEmergency(rs.getBoolean("Emergency"));
				booking.setStatus(rs.getString("Status"));
				booking.setUserId(rs.getString("UserId"));
				booking.setFacilityName(rs.getString("FacilityName"));
				booking.setId(rs.getString("Id"));
				booking.setReason(rs.getString("Reason"));
				booking.setBookOn(rs.getDate("BookOn"));
				booking.setValidateOn(rs.getDate("ValidateOn"));
				booking.setEndDate(rs.getDate("EndDate"));
				System.out.println(booking.toString());
				list.add(booking);

			}
			conn.commit();
			prep.close();
		} catch (Exception e)
		{
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally
		{
			try
			{
				conn.rollback();
				conn.close();
			} catch (Exception e)
			{
			}
		}

		return list;
	}

	@Override
	public void deleteBooking(String id) throws DAOException
	{

		Connection conn = null;
		try
		{
			conn = MySQLUtility.getConnection();
			conn.setAutoCommit(false);
		} catch (ClassNotFoundException e1)
		{
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try
		{
			String sql = "Update Booking Set Status='Deleted' where Id=? ";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, id);
			ps.executeUpdate();
			conn.commit();
			ps.close();
		} catch (Exception e)
		{
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally
		{
			try
			{
				conn.rollback();
				conn.close();
			} catch (Exception e)
			{
			}
		}
	}

	public void approveBooking(String id) throws DAOException
	{

		Connection conn = null;
		try
		{
			conn = MySQLUtility.getConnection();
			conn.setAutoCommit(false);
		} catch (ClassNotFoundException e1)
		{
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try
		{
			String sql = "Update Booking Set Status='Approved' where Id=? ";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, id);
			ps.executeUpdate();
			conn.commit();
			ps.close();
		} catch (Exception e)
		{
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally
		{
			try
			{
				conn.rollback();
				conn.close();
			} catch (Exception e)
			{
			}
		}
	}

	public void rejectBooking(String id) throws DAOException
	{

		Connection conn = null;
		try
		{
			conn = MySQLUtility.getConnection();
			conn.setAutoCommit(false);
		} catch (ClassNotFoundException e1)
		{
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try
		{
			String sql = "Update Booking Set Status='Rejected' where Id=? ";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, id);
			ps.executeUpdate();
			conn.commit();
			ps.close();
		} catch (Exception e)
		{
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally
		{
			try
			{
				conn.rollback();
				conn.close();
			} catch (Exception e)
			{
			}
		}
	}

	@Override
	public void cancelBooking(String id) throws DAOException
	{

		Connection conn = null;
		try
		{
			conn = MySQLUtility.getConnection();
			conn.setAutoCommit(false);
		} catch (ClassNotFoundException e1)
		{
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try
		{
			String sql = "Update Booking Set Status='Cancelled' where Id=? ";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, id);
			ps.executeUpdate();
			conn.commit();
			ps.close();
		} catch (Exception e)
		{
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally
		{
			try
			{
				conn.rollback();
				conn.close();
			} catch (Exception e)
			{
			}
		}
	}

}
